<%@ page language="java" import="java.sql.*" %>
<%@page import="java.text.SimpleDateFormat"%>

<%
    String driver = "org.gjt.mm.mysql.Driver";
    Class.forName(driver).newInstance();

    Connection con=null;
    ResultSet rst=null;
    Statement stmt=null;

    try{
        String url="jdbc:mysql://localhost:3306/progin_171_13509008?user=root";
        con=DriverManager.getConnection(url);
        stmt=con.createStatement();
    }
    catch(Exception e){
        System.out.println(e.getMessage());
    }

    String userInsert =request.getParameter("userInsert");
    String idCommentTemp= request.getParameter("id");
    String text = request.getParameter("text");
    int id_temp = (Integer)(session.getAttribute("id_content"));
    
    int idComment = Integer.parseInt(idCommentTemp);
/*    String userInsert =(String)(session.getAttribute("userInsert"));
    int idComment= (Integer)(session.getAttribute("id"));
    String text = (String)(session.getAttribute("text"));
    int id_temp = (Integer)(session.getAttribute("id_content"));
*/    
    // Get current datetime
    java.util.Date now = new java.util.Date();
    String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
    String strDateNew = sdf.format(now) ;
    
    String queryInsertComment = "INSERT INTO comment (comment_text, comment_time, id_content) VALUES ('"+text+"','"+strDateNew+"',"+id_temp+")";
    try{ 
        int j;
        j = stmt.executeUpdate(queryInsertComment);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    
    int curIDComment = 0;
    
    String qCount = "SELECT MAX(id_comment) FROM comment";
    try{ 
        rst = stmt.executeQuery(qCount);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    while(rst.next()){
        curIDComment = Integer.parseInt(rst.getString("MAX(id_comment)"));
    }
    
    String queryInsertCommentRelation = "INSERT INTO user_comment (id_comment, user_name) VALUES ("+curIDComment+", '"+userInsert+"')";
    try{ 
        int j;
        j = stmt.executeUpdate(queryInsertCommentRelation);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    
    
    
    
    // id Content nanti diubah pake session dari user
    String queryGetTotalComment = "select count(id_comment) as totalComment  from comment where id_content="+id_temp+"";
    try{ 
        rst = stmt.executeQuery(queryGetTotalComment);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    while (rst.next()){
        out.println("<h3 id=\"totalComment\">Total Comment : "+rst.getString("totalComment")+"</h3>");
    }
    
    
    //String querySelectComment = "select id_comment,user_name, comment_text, Concat ( Floor(Hour(TIMEDIFF (comment_time, '"+strDateNew+"'))/24), ' days ', MOD(HOUR(TIMEDIFF(comment_time, '"+strDateNew+"')), 24), ' hours ', MINUTE (TIMEDIFF(comment_time, '"+strDateNew+"')), ' minutes')as time from comment natural join user_comment where comment.id_content ="+id_temp+" order by time";
    String qu1 ="select id_comment,user_name, comment_text, Concat ( Floor(Hour(TIMEDIFF (comment_time, '"+strDateNew+"'))/24), ' days ',";
    String qu2="MOD(HOUR(TIMEDIFF(comment_time, '"+strDateNew+"')), 24), ' hours ',";
    String qu3 ="MINUTE (TIMEDIFF(comment_time, '"+strDateNew+"')), ' minutes')as time from";
    String qu4 = "comment natural join user_comment where comment.id_content ="+id_temp+" order by time";
    String querySelectComment= qu1+qu2+qu3+qu4;
    try{ 
        rst = stmt.executeQuery(querySelectComment);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    
    while (rst.next()){
        out.println("<div class='comment_list'>");
        out.println("<img src='images/logo.png' width='50px' height='50px' style='float: left;'></img>");
        out.println("<button type='button' onclick='deleteComment(\""+userInsert+"\","+rst.getString("id_comment")+")' style='float: right;'>X</button>");
        out.println("<h5>"+rst.getString("user_name")+"</h5><br/>");
        out.println("<h5>"+rst.getString("time")+"</h5><br/>");
        out.println("<h3>"+rst.getString("comment_text")+"</h3>");
        out.println("</div>");
    }
    
    
    // Check for achievement
    // get next id_content for image naming
    int totalComment = 0;
    String qCountTotalComment = "select count(*)as totalComment from user_comment where user_name ='"+userInsert+"'";
    try{ 
        rst = stmt.executeQuery(qCountTotalComment);
    } catch (SQLException e){
        out.println("Caught SQL Exception: " + e);
    }
    while (rst.next()){
        totalComment = Integer.parseInt(rst.getString("totalComment"));
    }
    if (totalComment == 1){
        //Insert Achievement ke user
        
        try{ 
            int j;
            j = stmt.executeUpdate("Insert into user_achievement (user_name, id_achievement) VALUES ('"+userInsert+"', 3)");
        } catch (SQLException e){
            out.println("Caught SQL Exception: " + e);
        }
    }
    if (totalComment == 20){
        //Insert Achievement ke user
        
        try{ 
            int j;
            j = stmt.executeUpdate("Insert into user_achievement (user_name, id_achievement) VALUES ('"+userInsert+"', 4)");
        } catch (SQLException e){
            out.println("Caught SQL Exception: " + e);
        }
    }
    response.sendRedirect("detail.jsp");
    
%>
